package com.lxl.workbench.dao;

import com.lxl.commons.utils.DbUtil;
import com.lxl.settings.entity.User;
import com.lxl.workbench.entity.Category;
import com.lxl.workbench.entity.CategoryStaff;
import com.lxl.workbench.entity.CommonStaff;
import com.lxl.workbench.entity.Curator;
import javax.servlet.jsp.jstl.sql.Result;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 北上寻海
 */
public class StaffDaoImpl implements StaffDao {
    @Override
    public Result selectStaffFromCommon(String staffName, String staffSex) throws SQLException {
        String sql = "select id,staff_name,gender,phone,user_id,permissions from tbl_common_staff";
        //先定义一个list集合来存放sql语句
        return getResult(staffName, staffSex, sql);

    }

    @Override
    public Result selectStaffFromCategory(String staffName, String staffSex) throws SQLException {
        String sql = "select id,staff_name,gender,phone,manage_type,permissions,user_id from tbl_category_staff";
        //先定义一个list集合来存放sql语句
        return getResult(staffName, staffSex, sql);

    }

    private Result getResult(String staffName, String staffSex, String sql) throws SQLException {
        List<String> list = new ArrayList<>();
        if (staffName != null && !"".equals(staffName)) {
            String sql1 = "staff_name = '" + staffName + "'";
            list.add(sql1);
        }
        if (staffSex != null && !"".equals(staffSex)) {
            String sql2 = "gender = '" + staffSex + "'";
            list.add(sql2);
        }
        if (list.size() > 0) {
            //list转数组+String类的拼接
            sql += " where " + String.join(" and ", list.toArray(new String[0]));
        }
        return DbUtil.select(sql);
    }

    @Override
    public Result selectCategoryStaffByManageId(String categoryId) throws SQLException {
        String sql = "select id,staff_name,gender,phone,manage_type,permissions,user_id from tbl_category_staff where manage_type = '"+categoryId+"' ";
        return DbUtil.select(sql);
    }

    @Override
    public Result selectCommonStaffById(String id) throws SQLException {
        String sql = "select id,staff_name,gender,phone,user_id,permissions from tbl_common_staff where id = '"+id+"' ";
        return DbUtil.select(sql);
    }

    @Override
    public void promoteStaff(CategoryStaff categoryStaff, String id) throws SQLException {
        String sql1 = "delete from tbl_common_staff where id = '"+id+"'";
        String sql2 = "insert into tbl_category_staff (id,staff_name,gender,phone,manage_type,permissions,user_id) values ('"+categoryStaff.getId()+"','"+categoryStaff.getStaffName()+"','"+categoryStaff.getGender()+"','"+categoryStaff.getPhone()+"','"+categoryStaff.getManageType()+"','"+categoryStaff.getPermissions()+"','"+categoryStaff.getUserId()+"')";
        DbUtil.update(sql1,sql2);
    }

    @Override
    public void resignStaffByIds(String[] delIds,String [] userIds) throws SQLException {
        //普通员工表
        StringBuilder sql1 = new StringBuilder("delete from tbl_common_staff where id in ");
        sql1.append("(");
        for (String delId : delIds) {
            sql1.append("'").append(delId).append("',");
        }
        sql1 = new StringBuilder(sql1.substring(0, sql1.length() - 1));
        sql1.append(")");

        //书目管理员表
        StringBuilder sql2 = new StringBuilder("delete from tbl_category_staff where id in ");
        sql2.append("(");
        for (String delId : delIds) {
            sql2.append("'").append(delId).append("',");
        }
        sql2 = new StringBuilder(sql2.substring(0, sql2.length() - 1));
        sql2.append(")");

        //tbl_user表
        StringBuilder sql3 = new StringBuilder("delete from tbl_user where id in ");
        sql3.append("(");
        for (String userId : userIds) {
            sql3.append("'").append(userId).append("',");
        }
        sql3 = new StringBuilder(sql3.substring(0, sql3.length() - 1));
        sql3.append(")");

        DbUtil.update(sql1.toString(),sql2.toString(),sql3.toString());
    }

    @Override
    public Result selectCommonStaffForUserId(String[] delIds) throws SQLException {
        //普通员工表
        StringBuilder sql = new StringBuilder("select id,staff_name,gender,phone,user_id,permissions from tbl_common_staff where id in ");
        sql.append("(");
        for (String delId : delIds) {
            sql.append("'").append(delId).append("',");
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(")");
        return DbUtil.select(sql.toString());
    }

    @Override
    public Result selectCategoryStaffForUserId(String[] delIds) throws SQLException {
        //书目管理员表
        StringBuilder sql = new StringBuilder("select id,staff_name,gender,phone,manage_type,permissions,user_id from tbl_category_staff where id in ");
        sql.append("(");
        for (String delId : delIds) {
            sql.append("'").append(delId).append("',");
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(")");
        return DbUtil.select(sql.toString());
    }

    @Override
    public void demoteStaff(List<CommonStaff> commonStaffList, String [] categoryIds) throws SQLException {

        //普通员工表
        StringBuilder sql1 = new StringBuilder("insert into tbl_common_staff (id,staff_name,gender,phone,user_id,permissions) values ");
        for(CommonStaff commonStaff : commonStaffList){
            sql1.append("(");
            sql1.append("'").append(commonStaff.getId()).append("','").append(commonStaff.getStaffName()).append("','").append(commonStaff.getGender()).append("','").append(commonStaff.getPhone()).append("','").append(commonStaff.getUserId()).append("','").append(commonStaff.getPermissions()).append("'");
            sql1.append("),");
        }
        sql1 = new StringBuilder(sql1.substring(0, sql1.length() - 1));

        //书目管理员表
        StringBuilder sql2 = new StringBuilder("delete from tbl_category_staff where id in ");
        sql2.append("(");
        for (String id : categoryIds) {
            sql2.append("'").append(id).append("',");
        }
        sql2 = new StringBuilder(sql2.substring(0, sql2.length() - 1));
        sql2.append(")");

        DbUtil.update(sql1.toString(),sql2.toString());
    }

    @Override
    public Result selectCuratorById(String id) throws SQLException {
        String sql = "select id,staff_name,gender,phone,email,user_id,permissions from tbl_curator where id = '"+id+"'";
        return DbUtil.select(sql);
    }

    @Override
    public Result selectCuratorByIds(String[] ids) throws SQLException {
        StringBuilder sql = new StringBuilder("select id,staff_name,gender,phone,email,user_id,permissions from tbl_curator where id in ");
        sql.append("(");
        for (String id : ids) {
            sql.append("'").append(id).append("',");
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(")");
        return DbUtil.select(sql.toString());
    }

    @Override
    public void addCommonStaffByCondition(User user, CommonStaff commonStaff) throws SQLException {
        String userSql = "insert into tbl_user values('"+user.getId()+"','"+user.getUsername()+"','"+user.getPassword()+"')";
        String commonStaffSql = "insert into tbl_common_staff values('"+commonStaff.getId()+"','"+commonStaff.getStaffName()+"','"+commonStaff.getGender()+"','"+commonStaff.getPhone()+"','"+commonStaff.getUserId()+"','"+commonStaff.getPermissions()+"')";
        DbUtil.update(userSql,commonStaffSql);
    }

    @Override
    public void addCategoryStaffByCondition(User user, CategoryStaff categoryStaff) throws SQLException {
    String userSql = "insert into tbl_user (id,user_name,password) values ('"+user.getId()+"','"+user.getUsername()+"','"+user.getPassword()+"')";
    String categoryStaffSql = "insert into tbl_category_staff (id,staff_name,gender,phone,manage_type,permissions,user_id) values ('"+categoryStaff.getId()+"','"+categoryStaff.getStaffName()+"','"+categoryStaff.getGender()+"','"+categoryStaff.getPhone()+"','"+categoryStaff.getManageType()+"','"+categoryStaff.getPermissions()+"','"+categoryStaff.getUserId()+"')";
    DbUtil.update(userSql,categoryStaffSql);
    }

}
